CREATE PROCEDURE [dbo].[CreateBalanceSheet]
    @StartDate VARCHAR(10),
    @EndDate VARCHAR(10),
    @AccountLevel SMALLINT,
    @ContainParentLevel BIT,
    @StartAccountCode VARCHAR(20),
    @EndAccountCode VARCHAR(20),
    @StartNoteNumber FLOAT,
    @EndNoteNumber FLOAT,
    @StoreCode VARCHAR(3) = NULL
AS
DECLARE @ResultTable TABLE
(
    AccountCode VARCHAR(20),
    AccountName NVARCHAR(90),
    AccountLevel SMALLINT,
    Bedeh MONEY,
    Bestan MONEY,
    RemainBedeh MONEY,
    RemainBestan MONEY,
    StoreCode VARCHAR(3)
);

DECLARE @Sanad TABLE
(
    AccountCode VARCHAR(20),
    Bedeh MONEY,
    Bestan MONEY,
    StoreCode VARCHAR(3)
);
DECLARE @VarStartDate SMALLDATETIME = dbo.ShamsiToMiladi(@StartDate);
DECLARE @VarEndDate SMALLDATETIME = dbo.ShamsiToMiladi(@EndDate);

IF ISNULL(@StoreCode, '0') = '0'
   OR @StoreCode = ''
    INSERT INTO @Sanad
    SELECT Acc_Code,
           SUM(BEDEH) Bedeh,
           SUM(BESTAN) Bestan,
           ''
    FROM dbo.Sanad WITH (NOLOCK)
        INNER JOIN HeadS
            ON CAST(dbo.HeadS.Tarikh_User AS DATE)
               BETWEEN @VarStartDate AND @VarEndDate
               AND HeadS.Id_Sanad = Sanad.Id_Sanad
    WHERE Nu_Manual
          BETWEEN @StartNoteNumber AND @EndNoteNumber
          AND Acc_Code
          BETWEEN @StartAccountCode AND @EndAccountCode
    GROUP BY Acc_Code;
ELSE
    INSERT INTO @Sanad
    SELECT Acc_Code,
           SUM(BEDEH) Bedeh,
           SUM(BESTAN) Bestan,
           dbo.Sanad.Store_Code
    FROM dbo.Sanad WITH (NOLOCK)
        INNER JOIN HeadS
            ON CAST(dbo.HeadS.Tarikh_User AS DATE)
               BETWEEN @VarStartDate AND @VarEndDate
               AND HeadS.Id_Sanad = Sanad.Id_Sanad
    WHERE Nu_Manual
          BETWEEN @StartNoteNumber AND @EndNoteNumber
          AND Acc_Code
          BETWEEN @StartAccountCode AND @EndAccountCode
          AND Sanad.Store_Code = @StoreCode
    GROUP BY Acc_Code,
             dbo.Sanad.Store_Code;

DECLARE @Acc TABLE
(
    AccountCode VARCHAR(20),
    AccountName NVARCHAR(90),
    ParentCode INT,
    AutoId INT,
    AccountLevel SMALLINT,
    HasChild BIT,
    Bedeh MONEY,
    Bestan MONEY,
    StoreCode VARCHAR(3)
);
INSERT INTO @Acc
SELECT Account.Acc_Code,
       Account.Acc_Name,
       Account.Parent_Code,
       Account.AutoId,
       Account.Acc_Level,
       Account.Has_Child,
       Sanad.Bedeh,
       Sanad.Bestan,
       CASE
           WHEN ISNULL(@StoreCode, '0') = '0'
                OR @StoreCode = '' THEN
               ''
           ELSE
               Sanad.StoreCode
       END
FROM Account
    LEFT JOIN @Sanad Sanad
        ON Sanad.AccountCode = Account.Acc_Code
WHERE (
          Has_Child = 0
          AND
          (
              Sanad.Bedeh > 0
              OR Sanad.Bestan > 0
          )
      )
      OR (Has_Child = 1)
ORDER BY Acc_Code;
WITH CTE
AS (SELECT Acc.AutoId,
           Acc.ParentCode,
           Acc.AccountCode,
           Acc.AccountName,
           Acc.AccountLevel,
           CASE
               WHEN Acc.HasChild = 0 THEN
                   Acc.Bedeh
               ELSE
                   0
           END Bedeh,
           CASE
               WHEN Acc.HasChild = 0 THEN
                   Acc.Bestan
               ELSE
                   0
           END Bestan,
           Acc.HasChild,
           Acc.StoreCode
    FROM @Acc Acc
    UNION ALL
    SELECT Acc.AutoId,
           Acc.ParentCode,
           Acc.AccountCode,
           Acc.AccountName,
           Acc.AccountLevel,
           CTE.Bedeh,
           CTE.Bestan,
           Acc.HasChild,
           CTE.StoreCode
    FROM @Acc Acc
        JOIN CTE
            ON Acc.AutoId = CTE.ParentCode)
INSERT INTO @ResultTable
(
    AccountCode,
    AccountName,
    AccountLevel,
    Bedeh,
    Bestan,
    RemainBedeh,
    RemainBestan,
    StoreCode
)
SELECT AccountCode,
       AccountName,
       CTE.AccountLevel,
       SUM(Bedeh) Bedeh,
       SUM(Bestan) Bestan,
       CASE
           WHEN SUM(Bedeh - Bestan) > 0 THEN
               SUM(Bedeh - Bestan)
           ELSE
               0
       END,
       CASE
           WHEN SUM(Bestan - Bedeh) > 0 THEN
               SUM(Bestan - Bedeh)
           ELSE
               0
       END,
       CTE.StoreCode
FROM CTE
GROUP BY AccountCode,
         AccountName,
         StoreCode,
         CTE.AccountLevel
HAVING SUM(Bedeh) > 0
       OR SUM(Bestan) > 0
ORDER BY AccountCode;

DECLARE @SumBedeh MONEY,
        @SumBestan MONEY,
        @SumRemainBedeh MONEY,
        @SumRemainBestan MONEY;


SELECT @SumBedeh = SUM(Result.Bedeh),
       @SumBestan = SUM(Result.Bestan),
       @SumRemainBedeh = SUM(RemainBedeh),
       @SumRemainBestan = SUM(RemainBestan)
FROM @ResultTable Result
    INNER JOIN Account
        ON Account.Acc_Code = Result.AccountCode
WHERE (
          @ContainParentLevel = 1
          AND AccountLevel = 1
      )
      OR
      (
          @ContainParentLevel = 0
          AND AccountLevel = @AccountLevel
      );

SELECT R.*,
       @SumBedeh SumBedeh,
       @SumBestan SumBestan,
       @SumRemainBedeh SumRemainBedeh,
       @SumRemainBestan SumRemainBestan
FROM @ResultTable R
WHERE (
          (
              @ContainParentLevel = 0
              AND AccountLevel = @AccountLevel
          )
          OR
          (
              @ContainParentLevel = 1
              AND AccountLevel <= @AccountLevel
          )
      );